package com.ruoyi.screen.repository;

import com.ruoyi.screen.dto.CountyUserCountDTO;
import com.ruoyi.screen.dto.CreditDistributionDTO;
import com.ruoyi.screen.dto.IncomeDistributionDTO;
import com.ruoyi.screen.dto.UserProfileDistributionDTO;
import com.ruoyi.screen.entity.UserProfile;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface UserProfileRepository extends JpaRepository<UserProfile, Long> {
    @Query("SELECT new com.ruoyi.screen.dto.UserProfileDistributionDTO('creditScore', COUNT(u)) " +
            "FROM User u GROUP BY u.creditScore")
    List<UserProfileDistributionDTO> countByCreditScore();

    @Query("SELECT new com.ruoyi.screen.dto.UserProfileDistributionDTO('age', COUNT(u)) " +
            "FROM UserProfile u GROUP BY u.age")
    List<UserProfileDistributionDTO> countByAge();

    @Query("SELECT new com.ruoyi.screen.dto.UserProfileDistributionDTO('gender', COUNT(u)) " +
            "FROM UserProfile u GROUP BY u.gender")
    List<UserProfileDistributionDTO> countByGender();

    @Query("SELECT new com.ruoyi.screen.dto.UserProfileDistributionDTO('jobType', COUNT(u)) " +
            "FROM UserProfile u GROUP BY u.jobType")
    List<UserProfileDistributionDTO> countByJobType();

    @Query(value = "SELECT " +
            "county, " +
            "MIN(credit_score) as minScore, " +
            "MAX(credit_score) as maxScore, " +
            "AVG(credit_score) as avgScore, " +
            "PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY credit_score) as q1, " +
            "PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY credit_score) as q3 " +
            "FROM user_profile GROUP BY county",
            nativeQuery = true)
    List<Object[]> calculateCreditScoreDistribution();

    @Query("SELECT new com.ruoyi.screen.dto.IncomeDistributionDTO(u.county, AVG(u.annualIncome)) " +
            "FROM UserProfile u GROUP BY u.county")
    List<IncomeDistributionDTO> getAvgIncomeByCounty();

    @Query("SELECT new com.ruoyi.screen.dto.CountyUserCountDTO(u.county, COUNT(u)) " +
            "FROM UserProfile u GROUP BY u.county")
    List<CountyUserCountDTO> countUsersByCounty();
}